Back to Main Menu

Reverse Asset Split

Overview

Changes need to be made in four tables 

  • Disposed Assets
    • remove disposal record
  • Subassets
    • remove split records
    • possible need to reassociate if the asset is in the middle of a hierarchy
  • Asset Archive Table
    • remove new assets
  • Asset Table
    • remove new assets
    • update old asset
      • acquisition Type
      • invisible

 

Sample Check scripts

select da.* from DisposedAssets da inner join WaterMains wm on wm.uniqueID=da.parentID and da.parentName = 'WaterMains'
where wm.assetID = '207366'

select * from subassets da inner join WaterMains wm on wm.uniqueID=da.parentUniqueID and da.parentName = 'WaterMains'
where wm.assetID = '207366'

select wma.uniqueID,wma._Timestamp,wma.invisible,wma.assetID,wma.assetName,wma.pipeAcquisitionType from WaterMains_archived wma inner join WaterMains wm on wm.uniqueID = wma.uniqueID
where wm.assetID in ('414247','414248','207366')-- or uniqueID = 121146


select uniqueID,_Timestamp,invisible,assetID,assetName,pipeAcquisitionType from WaterMains
where assetID in ('414247','414248','207366')-- or uniqueID = 121146

 

Sample Removal scripts

/*
delete da from DisposedAssets da inner join WaterMains wm on wm.uniqueID=da.parentID and da.parentName = 'WaterMains'
where wm.assetID = '207366'

delete da from subassets da inner join WaterMains wm on wm.uniqueID=da.parentUniqueID and da.parentName = 'WaterMains'
where wm.assetID = '207366'

delete wma from WaterMains_archived wma inner join WaterMains wm on wm.uniqueID = wma.uniqueID
where wm.assetID in ('414247','414248')

delete wm from WaterMains wm where wm.assetID in ('414247','414248')

update WaterMains set 
invisible = 0,
pipeAcquisitionType = 'Existing Asset' 
where assetId = '207366'

*/